package com.is.control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import com.is.util.Pagination;
import com.is.dbconn.DBConnect;
import com.is.model.Dictation;

public class DictationControl {
	public static Pagination pageInstance = null;
	
	public static boolean insertDictation(Dictation d) {
		DBConnect dbConnect= new DBConnect();
		String sql= "insert into dictation (stuId,timeId,level) values(?,?,?)";
		dbConnect.prepareStatement(sql);
		try {
			setValue(d, dbConnect);
			if(dbConnect.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		    e.printStackTrace();
		    System.out.println("insertDictaion error");
		}finally{
			dbConnect.close();
		}
		return false;
		
	}
	
	//事务提交听写结果
	public static boolean insertDictation(List<Dictation> dics){
		DBConnect dbConnect = new DBConnect();
		String sql= "insert into dictation (stuId,timeId,level) values(?,?,?)";
		PreparedStatement pstmt = null;
		dbConnect.setAutoCommit(false);
		dbConnect.prepareStatement(sql);
		pstmt = dbConnect.getPreparedStatement();
		try {
			for(Dictation d: dics){
				int index = 1;
				pstmt.setInt(index++, d.getStuId());
				pstmt.setInt(index++, d.getTimeId());
				pstmt.setString(index++, d.getLevel());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			dbConnect.commit();
			dbConnect.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			dbConnect.rollback();
			e.printStackTrace();
			System.out.println("insertDictation(List) ERROR");
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	public static boolean updateDictation(Dictation d) {
		DBConnect dbConnect= new DBConnect();
		String sql= "update dictation set stuId=?,timeId=?,level=? where id='"+d.getId()+"'";
		dbConnect.prepareStatement(sql);
		try {
			setValue(d, dbConnect);
			if(dbConnect.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		    e.printStackTrace();
		    System.out.println("update error");
		}finally{
			dbConnect.close();
		}
		return false;
		
	}
	public static boolean updateDictationByIdAndTimeId(int stuId,int timeId,String level){
		DBConnect dbc = new DBConnect();
		String sql = "update dictation set level = '"+ level +"' where stuId='"+stuId+"' and timeId='"+timeId+"'";
		dbc.prepareStatement(sql);
		try{
			if(dbc.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("updateDictationByIdAndTimeId ERROR");
		} finally{
			dbc.close();
		}
		return false;
	}
	public static void setValue(Dictation d,DBConnect dbc){
		//调用此方法需注意字段顺序(与sql语句保持一致)
		int index=1;
		try{
		dbc.setInt(index++, d.getStuId());
		dbc.setInt(index++, d.getTimeId());	
		dbc.setString(index++, d.getLevel());
		
	}catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		System.out.println("setValue方法出错");
	}
	}
	
	public static void getRs(Dictation d,ResultSet rs){
		try {
			d.setId(rs.getInt("id"));
			d.setTimeId(rs.getInt("timeId"));
			d.setLevel(rs.getString("level"));
			d.setStuId(rs.getInt("stuId"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getRs出错");
		}
	}
	
	//删除某一次听写的所有记录
	public static boolean deleteDictationByTimeId(int timeId){
		DBConnect dbConnect = new DBConnect();
		String sql = "delete from dictation where timeId = '"+timeId+"' ";
		dbConnect.prepareStatement(sql);
		try {
			if(dbConnect.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("deleteDictationByTimeId ERROR");
		}finally{
			dbConnect.close();
		}
		return false;
		}
	
	public static ArrayList<Dictation> getDictationsByTimeId(int timeId) {
		ArrayList<Dictation> dicts = new ArrayList<Dictation>();
		DBConnect dbConnect = new DBConnect();
		String sql = "select * from dictation where timeId='"+timeId+"'";
		dbConnect.prepareStatement(sql);
		ResultSet rSet=null;
		try {
			rSet = dbConnect.executeQuery();
			while(rSet.next()){
				Dictation dict = new Dictation();
				getRs(dict, rSet);
				dicts.add(dict);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getDictationsByTimeId ERROR");
		}finally{
			dbConnect.close();
		}
		return dicts;
	}
	
	/**
	 * 
	 * @param stuId
	 * @param level
	 * @param stuyear 
	 * @return
	 */
	
	public static int getCount(int stuId,String level,int courseClassId,int stuyear,String time1,String time2){
	    DBConnect dbConnect = new DBConnect();
	    String sTime = ParamStuyearControl.getStuyear(stuyear).getStartTime();
	    if(time1!=null&&!time1.equals("")){
	    	sTime = time1;
	    }
	    String eTime = ParamStuyearControl.getStuyear(stuyear).getEndTime();
	    if(time2!=null&&!time2.equals("")){
	    	eTime = time2;
	    }
	    int  count = 0;
	    String sql="select count(dictation.id) from dictation,dictation_time where dictation.timeId = dictation_time.id " +
	    		" and (dictation_time.time between '"+sTime+"' and '"+eTime+"') and dictation.level='"+level+"' " +
	    				" and dictation_time.courseClassId='"+courseClassId+"' and stuId = '"+stuId+"'";
	    dbConnect.prepareStatement(sql);
	    //System.out.println(sql);
	    ResultSet rSet = null;
	    try {
		rSet = dbConnect.executeQuery();
		if(rSet.next()){
		    count = rSet.getInt(1);
		}
	    } catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		System.out.println("getCountByLevelAndStuId ERROR");
	    }finally{
		dbConnect.close();
	    }
	    return count;
	}
	
	
	
	/**
	 * 
	 * @param stuId
	 * @param courseId
	 * @param time
	 * @param page
	 * @param rowsPerPage
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public static Collection<Object> getDictationsByMuti(String stuId,String courseId,String time,int page, int rowsPerPage) throws SQLException, Exception {
		String sql="";
		if(stuId==null||stuId.equals("")){
			stuId="%";
		}
		if(courseId==null||courseId.equals("")){
			courseId="%";
		}
		if(time==null||time.equals("")){
			time = "%";
		}
		sql = "select * from dictation where stuId like '"+stuId+"' and courseId ='"+courseId+"' and time like '"+time+"' ";
		pageInstance = new DictationControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}
      
      
}
class DictationControlPage extends Pagination {
           public DictationControlPage(String sql, int rowsPerPage) {
               super(sql, rowsPerPage, 1);
}

        protected Collection<Dictation> packResultSet(ResultSet rs) throws SQLException {
	Collection<Dictation> ds = new ArrayList<Dictation>();
	Dictation d = null;
	while (rs.next()) {
		d=new Dictation();
		d.setId(rs.getInt("id"));
		d.setTimeId(rs.getInt("timeId"));
		d.setLevel(rs.getString("level"));
		d.setStuId(rs.getInt("stuId"));
	        ds.add(d);
		d = null;
	}
	return ds;
}

}

